package com.fishsticks.model;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import com.fishsticks.R;
import com.fishsticks.view.RegisterActivity;
import com.google.android.maps.GeoPoint;

import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.widget.Toast;

public class DatabaseManager extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "fishsticksToDo.db";
	private static final int 	DATABASE_VERSION = 1;
	private static final String USER_TABLE_NAME = "users";
	private static final String TODO_TABLE_NAME = "to_do_item";
	private static final String CATEGORY_TABLE_NAME = "category";
	private static final String DATE_TABLE_NAME = "date";
	private static final String LOCATION_TABLE_NAME = "location";
	private static String loggedInUser;

	private static final String USER_TABLE_CREATE = "CREATE TABLE " + USER_TABLE_NAME + " (" +
			"userID integer primary key autoincrement, " +
			"username varchar(20) unique not null, " +
			"password varchar(40) not null, " +
			"first_name varchar(20) not null, " +
			"last_name varchar(20) not null )";

	private static final String DATE_TABLE_CREATE = "CREATE TABLE " + DATE_TABLE_NAME + " (" +
			"dateID integer primary key autoincrement, " +
			"day integer not null, " +
			"month integer not null, " +
			"year integer not null, " +
			"user varchar(20) not null references " + USER_TABLE_NAME + "(username) )";

	private static final String LOCATION_TABLE_CREATE = "CREATE TABLE " + LOCATION_TABLE_NAME + " (" +
			"locationID integer primary key autoincrement, " +
			"latitude real not null, " +
			"longitude real not null, " +
			"city varchar(20), " +
			"state varchar(2), " +
			"user varchar(20) not null references " + USER_TABLE_NAME + "(username) )";

	private static final String CATEGORY_TABLE_CREATE = "CREATE TABLE " + CATEGORY_TABLE_NAME + " (" +
			"categoryID integer primary key autoincrement, " +
			"user varchar(20) not null references " + USER_TABLE_NAME + "(username), " +
			"category_name varchar(20) not null )";

	private static final String TODO_TABLE_CREATE = "CREATE TABLE " + TODO_TABLE_NAME + " (" +
			"todoID integer primary key autoincrement, " +
			"title varchar(40) not null, " +
			"description varchar(40), " +
			"date integer not null references " + DATE_TABLE_NAME + " (dateID), " +
			"user varchar(20) not null references " + USER_TABLE_NAME + " (username), " +
			"category integer not null references " + CATEGORY_TABLE_NAME + " (categoryID), " +
			"location integer references " + LOCATION_TABLE_NAME + " (locationID), " +
			"completed varchar(1) )";		//Completed should be either 'N' or 'Y'



	public DatabaseManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}



	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(USER_TABLE_CREATE);
		db.execSQL(DATE_TABLE_CREATE);
		db.execSQL(LOCATION_TABLE_CREATE);
		db.execSQL(CATEGORY_TABLE_CREATE);
		db.execSQL(TODO_TABLE_CREATE);

		//would use register here but it was creating
		//a recursive call error
		ContentValues new_user = new ContentValues();
    	new_user.put("first_name", "Fishsticks");
    	new_user.put("last_name", "DoYouLikeEm");
    	new_user.put("username", "default");
    	new_user.put("password", "default");
		db.insert(DatabaseManager.USER_TABLE_NAME, null, new_user);


		//inserts default category values
		// 'Personal', 'School' and 'Work'
		ContentValues new_category = new ContentValues();
		new_category.put("user", "default");
		new_category.put("category_name", "Personal");
		db.insert(CATEGORY_TABLE_NAME, null, new_category);

		new_category = new ContentValues();
		new_category.put("user", "default");
		new_category.put("category_name", "School");
		db.insert(CATEGORY_TABLE_NAME, null, new_category);

		new_category = new ContentValues();
		new_category.put("user", "default");
		new_category.put("category_name", "Work");
		db.insert(CATEGORY_TABLE_NAME, null, new_category);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		if(oldVersion == 0 || newVersion == 0) {
			db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + DATE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + LOCATION_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + TODO_TABLE_NAME);

			db.execSQL(USER_TABLE_CREATE);
			db.execSQL(DATE_TABLE_CREATE);
			db.execSQL(LOCATION_TABLE_CREATE);
			db.execSQL(CATEGORY_TABLE_CREATE);
			db.execSQL(TODO_TABLE_CREATE);

			//would use register here but it was creating
			//a recursive call error
			ContentValues new_user = new ContentValues();
	    	new_user.put("first_name", "Fishsticks");
	    	new_user.put("last_name", "DoYouLikeEm");
	    	new_user.put("username", "default");
	    	new_user.put("password", "default");
			db.insert(DatabaseManager.USER_TABLE_NAME, null, new_user);


			//inserts default category values
			// 'Personal', 'School' and 'Work'
			ContentValues new_category = new ContentValues();
			new_category.put("user", "default");
			new_category.put("category_name", "Personal");
			db.insert(CATEGORY_TABLE_NAME, null, new_category);

			new_category = new ContentValues();
			new_category.put("user", "default");
			new_category.put("category_name", "School");
			db.insert(CATEGORY_TABLE_NAME, null, new_category);

			new_category = new ContentValues();
			new_category.put("user", "default");
			new_category.put("category_name", "Work");
			db.insert(CATEGORY_TABLE_NAME, null, new_category);
		}

		else {
			db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + DATE_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + LOCATION_TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + TODO_TABLE_NAME);

			onCreate(db);
		}
	}




	/**
	 * Checks if a specific username/password combination
	 * 		 is in the tables or not.
	 *
	 * @param username The username to check
	 * @param password The password to check
	 * @return A boolean representing whether or
	 * 		not they exist in the DB
	 */
	public boolean checkCredentials(String username, String password)
	{
		String[] columns = {"username"};
		String[] query_values = {username, password};

		Cursor c = this.getReadableDatabase().query(USER_TABLE_NAME, columns,
				"username=? AND password=?",
				query_values, null, null, null);
		if(c.getCount() > 0){
			loggedInUser = username;
			return true;
		} else {
			return false;
		}
	}





	/**
	 * Checks if a specific username is in the tables or not.
	 *
	 * @param username The username to check
	 * @return A boolean representing whether or not the
	 * 		username exists in the DB
	 */
	public boolean checkIfUsernameExists(String username)
	{
		String[] columns = {"username"};
		String[] query_values = {username};
		Cursor c = this.getReadableDatabase().query(USER_TABLE_NAME, columns,
				"username=?",
				query_values, null, null, null);
		if(c.getCount() > 0)
			return true;

		else
			return false;

	}





	/**
	 * Registers a new user in the DB; Checks to make sure the fields
	 * 		aren't blank and are valid, and checks if the passwords match
	 *
	 * @param first_name The user's first name
	 * @param last_name The user's last name
	 * @param username The user's desired username
	 * @param password The user's desired password
	 * @param password_confirm The re-entered password
	 * @return The error or success code depending on whether or not it was an error or a success
	 */
	public int register(String first_name, String last_name,
			String username, String password, String password_confirm) {

	    //AlertDialog reg_error_dialog = new AlertDialog.Builder(ra).create();
    	//reg_error_dialog.setTitle("Registration Error");
    	int error = -1;

    	if(first_name.equals("") || last_name.equals("") || username.equals("") || password.equals(""))
    		error = R.string.registration_error_code_blank_fields;

    	else if(!password.equals(password_confirm))
	    	error = R.string.registration_error_code_password_mismatch;

    	else if(password.length() > 40)
	    	error = R.string.registration_error_code_password_too_long;

    	else if(first_name.length() > 20 || last_name.length() > 20 || username.length() > 20)
	    	error = R.string.registration_error_code_names_too_long;



    	//If there were no errors
    	if(error == -1)
    	{
    		ContentValues new_user = new ContentValues();
	    	new_user.put("first_name", first_name);
	    	new_user.put("last_name", last_name);
	    	new_user.put("username", username);
	    	new_user.put("password", password);
			this.getWritableDatabase().insert(USER_TABLE_NAME, null, new_user);

			//inserts default category values
			// 'Personal', 'School' and 'Work' for this new user
			ContentValues new_category = new ContentValues();
			new_category.put("user", username);
			new_category.put("category_name", "Personal");
			this.getWritableDatabase().insert(CATEGORY_TABLE_NAME, null, new_category);

			new_category = new ContentValues();
			new_category.put("user", username);
			new_category.put("category_name", "School");
			this.getWritableDatabase().insert(CATEGORY_TABLE_NAME, null, new_category);

			new_category = new ContentValues();
			new_category.put("user", username);
			new_category.put("category_name", "Work");
			this.getWritableDatabase().insert(CATEGORY_TABLE_NAME, null, new_category);

    	}

		return error;
	}




	/**
	 * Adds a new to-do item in the DB for a specific user.
	 *
	 * @param title The title of the to-do
	 * @param description The description of the to-do
	 * @param date The due date of the to-do
	 * @param username The current logged in user
	 * @param cat The category of the to-do
	 * @param latitude The location's latitude value
	 * @param longitude The location"s longitude value
	 * @return A string resource representing whether
	 * 		or not the to-do item was inserted or not
	 */
	@SuppressWarnings("deprecation")
	public int addToDoItem(String title, String description, Date date, String username, Category cat, String latitude, String longitude)
	{
		int error = -1;

		if(title != null && title.equals("") || description.equals("") || latitude.equals("") || longitude.equals(""))
    		error = R.string.add_to_do_error_code_blank_fields;

		else if(date != null && date.before(new Date(Calendar.getInstance().get(Calendar.YEAR),
									 Calendar.getInstance().get(Calendar.MONTH),
									 Calendar.getInstance().get(Calendar.DATE))))
			error = R.string.add_to_do_error_code_date_too_early;

		else if(cat != null && cat.getName().equals("Select a category"))
			error = R.string.add_to_do_error_code_no_category;

		//else if(//latitude is invalid)
			//error = R.string.add_to_do_error_code_no_latitude;

		//else if(//longitude is invalid)
			//error = R.string.add_to_do_error_code_no_longitude;

		if(error == -1)
		{
			//String latitudeNoComma = latitude.substring(0, latitude.length()-1);

			ContentValues new_todo = new ContentValues();
	    	new_todo.put("title", title);
	    	new_todo.put("description", description);
	    	long dateID = insertDate(date.getDate(), date.getMonth(),date.getYear(), username);
	    	new_todo.put("date", dateID);
	    	new_todo.put("user", username);
	    	new_todo.put("category", cat.getName());
	    	long locID = insertLocation(latitude, longitude, username);
	    	new_todo.put("location", locID);
	    	new_todo.put("completed", "N");
			this.getWritableDatabase().insert(TODO_TABLE_NAME, null, new_todo);

			loggedInUser = username;
		}

		return error;
	}





	/**
	 * Returns all the to-do items for a specific user
	 *
	 * @param username The logged in user
	 * @return An arraylist of to-do objects
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> getToDoItems(String username)
	{
		ArrayList<ToDoItem> toDoItems = new ArrayList<ToDoItem>();

		if(username != "" && checkIfUsernameExists(username)) {

			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude, completed " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=?";

			String[] query_values = {username, username, username, username};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));

				boolean completed = false;
				
				if(c.getString(c.getColumnIndexOrThrow("completed")).equals("Y"))
					completed = true;

				toDoItems.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   completed, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDoItems;
	}



	/**
	 * Inserts a new location for a specific user in
	 * 		 the location database
	 *
	 * @param lat The location's latitude value
	 * @param lon The location"s longitude value
	 * @param username The user storing the location
	 *
	 * @return id of the new row
	 */
	public long insertLocation(String lat, String lon, String username) {
		ContentValues values = new ContentValues();
		values.put("latitude", Double.parseDouble(lat));
		values.put("longitude", Double.parseDouble(lon));
		values.put("user", username);
    	long locationID = this.getWritableDatabase().insert(LOCATION_TABLE_NAME, null, values);

    	return locationID;
	}


	/**
	 * Stores a new date in the date table
	 *
	 * @param day The day of the date
	 * @param month The month of the date
	 * @param year The year of the date
	 * @param username The user storing the date
	 *
	 * @return id of the new row
	 */
	public long insertDate(int day, int month, int year, String username) {
		ContentValues values = new ContentValues();
		values.put("day", day);
		values.put("month", month+1);
		values.put("year", year);
		values.put("user", username);

		long rowID = this.getWritableDatabase().insert(DATE_TABLE_NAME, null, values);

    	return rowID;
	}




	/**
	 * Gets all the user's specific categories
	 *
	 * @param username The user to get categories for.
	 * @return An arraylist of category objects
	 */
	public ArrayList<Category> getCategories(String username)
	{
		ArrayList<Category> categories = new ArrayList<Category>();

		if(username != "" && checkIfUsernameExists(username)) {
			String[] columns = {"category_name"};
			String[] query_values = {username};
			Cursor c = this.getReadableDatabase().query(CATEGORY_TABLE_NAME, columns,
					"user=?",
					query_values, null, null, null);

			//Cursor index starts at -1 so you
			//  have to move it forward to 0
			c.moveToNext();

			while(!c.isAfterLast()) {
				categories.add(new Category(c.getString(0)));
				c.moveToNext();
			}
		}

		return categories;
	}



	/**
	 * Adds a new category for the current user
	 *
	 * @param newCategory The name of the category to add
	 * @return An int error code or -1 if successful
	 */
	public int addCategory(String newCategory)
	{
		int error = -1;

		if(newCategory.equals(""))
    		error = R.string.add_category_error_code_blank_fields;

		if(error == -1)
		{
			ContentValues new_category = new ContentValues();
			new_category.put("category_name", newCategory);
			new_category.put("user", loggedInUser);
			this.getWritableDatabase().insert(CATEGORY_TABLE_NAME, null, new_category);
		}

		return error;
	}



	/**
	 * Returns the current logged in username
	 *
	 * @return The current user
	 */
	public String getLoggedInUser() {
		return loggedInUser;
	}
	
	
	/**
	 * Sets the current logged in username
	 *
	 * @param The current user
	 */
	public void setLoggedInUser(String name) {
		loggedInUser = name;
	}


	/**
	 * Finds to-do items that have a status of completed
	 *
	 * @param username The current logged in user
	 * @return A list of to do items
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> getCompletedItems(String username) {
		ArrayList<ToDoItem> toDoItems = new ArrayList<ToDoItem>();

		if(username != "" && checkIfUsernameExists(username)) {

			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=? and completed=?;";

			String[] query_values = {username, username, username, username, "Y"};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));



				toDoItems.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   true, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDoItems;
	}
	
	
	
	/**
	 * Finds to-do items that have a status of incomplete
	 *
	 * @param username The current logged in user
	 * @return A list of to do items
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> getIncompleteItems(String username) {
		ArrayList<ToDoItem> toDoItems = new ArrayList<ToDoItem>();

		if(username != "" && checkIfUsernameExists(username)) {

			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=? and completed=?;";

			String[] query_values = {username, username, username, username, "N"};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));



				toDoItems.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   false, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDoItems;
	}
	



	/**
	 * Gets to do items of a certain category if that category is legit
	 *
	 * @param username The current logged in user
	 * @param cat The category to filter by
	 * @return A list of to do items
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> getToDoItemsWithCategory(String username, Category cat) {
		ArrayList<ToDoItem> toDos = new ArrayList<ToDoItem>();

		if(categoryExists(cat, username) && username != "" && checkIfUsernameExists(username)) {
			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude, completed " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=? and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=?;";

			String[] query_values = {cat.getName(), username, username, username, username};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));

				boolean completed = false;
				
				if(c.getString(c.getColumnIndexOrThrow("completed")).equals("Y"))
					completed = true;

				toDos.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   completed, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDos;
	}
	
	
	
	
	
	
	/**
	 * Gets a to do item with a specific location/user
	 *
	 * @param username The current logged in user
	 * @param lat The latitude of the location to search for
	 * @param lon The longitude of the location to search for
	 * @return A to do item
	 */
	@SuppressWarnings("deprecation")
	public ToDoItem getToDoWithLocation(String username, String lat, String lon) {
		ToDoItem toDo = null;

		if(username != "" && checkIfUsernameExists(username)) {
			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude, completed " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  LOCATION_TABLE_NAME + ".latitude=" + lat + " and " + LOCATION_TABLE_NAME + ".longitude=" + lon + " and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=?;";

			String[] query_values = {username, username, username, username};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));

				boolean completed = false;
				
				if(c.getString(c.getColumnIndexOrThrow("completed")).equals("Y"))
					completed = true;

				toDo = new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   completed, c.getInt(c.getColumnIndexOrThrow("todoID")));
				c.moveToNext();
			}
		}

		return toDo;
	}








	/**
	 * Returns true if this category exists in the DB
	 * 		for this particular user
	 *
	 * @param cat The category to check
	 */
	public boolean categoryExists(Category cat, String username) {
		boolean rtn = false;

		if(checkIfUsernameExists(username)) {
			String[] columns = {"user", "category_name"};
			String[] query_values = {username, cat.getName()};
			Cursor c = this.getReadableDatabase().query(CATEGORY_TABLE_NAME, columns,
					"user=? and category_name=?",
					query_values, null, null, null);
			if(c.getCount() > 0)
				return true;

			else
				return false;
		}

		return rtn;
	}




	/**
	 * Gets to do items after a certain date
	 *
	 * @param username The current logged in user
	 * @param date The date to filter by
	 * @return A list of to do items
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> getToDoItemsAfterDate(String username, Date date) {
		ArrayList<ToDoItem> toDos = new ArrayList<ToDoItem>();

		if(username != "" && checkIfUsernameExists(username)) {

			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude, completed " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user=? and " + CATEGORY_TABLE_NAME + ".user=? and " +
									  LOCATION_TABLE_NAME + ".user=? and " + DATE_TABLE_NAME + ".user=? and ((" +
									  		"" + DATE_TABLE_NAME + ".year > ?) or (" +
									  				"" + DATE_TABLE_NAME + ".year = ? and " + DATE_TABLE_NAME + ".month > ?) or(" +
									  						"" + DATE_TABLE_NAME + ".year = ? and " + DATE_TABLE_NAME + ".month = ? and " + DATE_TABLE_NAME + ".day >= ?));";

			String[] query_values = {username, username, username, username,
					Integer.toString(date.getYear()),
					Integer.toString(date.getYear()), Integer.toString(date.getMonth()),
					Integer.toString(date.getYear()), Integer.toString(date.getMonth()),Integer.toString(date.getDate())};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, query_values);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));

				boolean completed = false;
				
				if(c.getString(c.getColumnIndexOrThrow("completed")).equals("Y"))
					completed = true;


				toDos.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
										   		        new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   completed, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDos;
	}



	/**
	 * Marks a to-do item as completed or not
	 * 
	 * @param toDo The to-do item to change
	 * @param completed A boolean representing whether 
	 * 				to mark it as completed or not
	 */
	public void markToDoAsCompleted(ToDoItem toDo, boolean completed) {
		String completedStr = "Y";
		if(!completed) completedStr = "N";
		String whereClause = "todoID=" + Integer.toString(toDo.getId());
		ContentValues args = new ContentValues();
		args.put("completed", completedStr);
		this.getWritableDatabase().update(TODO_TABLE_NAME, args, whereClause, null);

	}

	
	
	/**
	 * Helper method for markToDoAsCompleted(ToDoItem item, boolean completed)
	 * 
	 * @param toDo The to-do item to change
	 */
	public void markToDoAsCompleted(ToDoItem toDo){
		this.markToDoAsCompleted(toDo, true);
	}
	
	
	

	/**
	 * Deletes a user's to-do item
	 * 
	 * @param toDo The to-do item to delete
	 */
	public void deleteToDo(ToDoItem toDo) {
		//get the location and date id's
		String query = "SELECT location, date FROM " + TODO_TABLE_NAME + 
					  " WHERE todoID=" + Integer.toString(toDo.getId()) + " and user='" + loggedInUser + "'";
		Cursor c = this.getWritableDatabase().rawQuery(query, null);

		c.moveToFirst(); //first and only
		if(c.isAfterLast()) return; //nothing matched this toDo

		int locationId = c.getInt(c.getColumnIndexOrThrow("location"));
		int dateId = c.getInt(c.getColumnIndexOrThrow("date"));
		//delete the todo item as well as the location/date info

		String query2 = "DELETE FROM " + LOCATION_TABLE_NAME + 
						" WHERE locationID=" + Integer.toString(locationId) + " and user='" + loggedInUser + "'";
		this.getWritableDatabase().execSQL(query2);

		String query3 = "DELETE FROM " + DATE_TABLE_NAME + 
						" WHERE dateID=" + Integer.toString(dateId) + " and user='" + loggedInUser + "'";
		this.getWritableDatabase().execSQL(query3);

		//now delete the actual todo item

		String query4 = "DELETE FROM " + TODO_TABLE_NAME + 
						" WHERE todoID=" + Integer.toString(toDo.getId()) + " and user='" + loggedInUser + "'";
		this.getWritableDatabase().execSQL(query4);
	}
	
	
	/**
	 * Deletes a user's to-do item
	 * 
	 * @param toDo The to-do item to delete
	 */
	public void deleteCategory(Category cat) {
//		//get the location and date id's
//		String query = "SELECT location, date FROM " + TODO_TABLE_NAME + 
//					  " WHERE todoID=" + Integer.toString(toDo.getId()) + " and user='" + loggedInUser + "'";
//		Cursor c = this.getWritableDatabase().rawQuery(query, null);
//
//		c.moveToFirst(); //first and only
//		if(c.isAfterLast()) return; //nothing matched this toDo
//
//		int locationId = c.getInt(c.getColumnIndexOrThrow("location"));
//		int dateId = c.getInt(c.getColumnIndexOrThrow("date"));
//		//delete the todo item as well as the location/date info
//
//		String query2 = "DELETE FROM " + LOCATION_TABLE_NAME + 
//						" WHERE locationID=" + Integer.toString(locationId) + " and user='" + loggedInUser + "'";
//		this.getWritableDatabase().execSQL(query2);
//
//		String query3 = "DELETE FROM " + DATE_TABLE_NAME + 
//						" WHERE dateID=" + Integer.toString(dateId) + " and user='" + loggedInUser + "'";
//		this.getWritableDatabase().execSQL(query3);
//
//		//now delete the actual todo item
//
//		String query4 = "DELETE FROM " + TODO_TABLE_NAME + 
//						" WHERE todoID=" + Integer.toString(toDo.getId()) + " and user='" + loggedInUser + "'";
//		this.getWritableDatabase().execSQL(query4);
	}




	/**
	 * Gets to do items with a title or description
	 * 		matching the search term
	 *
	 * @param username The current logged in user
	 * @param searchTerm The search term to filter by
	 * @return A list of to do items
	 */
	@SuppressWarnings("deprecation")
	public ArrayList<ToDoItem> searchToDoList(String username, String searchTerm) {
		ArrayList<ToDoItem> toDoItems = new ArrayList<ToDoItem>();

		if(username != "" && checkIfUsernameExists(username)) {

			String getToDoListQuery = "select todoID, title, description, day, month, year, category, latitude, longitude " +
									  "from " + TODO_TABLE_NAME + ", " + DATE_TABLE_NAME + ", " + CATEGORY_TABLE_NAME + ", " +
									  LOCATION_TABLE_NAME + " " + "where " + TODO_TABLE_NAME + ".date=" + DATE_TABLE_NAME +
									  ".dateID and " + TODO_TABLE_NAME + ".location=" + LOCATION_TABLE_NAME + ".locationID and " +
									  TODO_TABLE_NAME + ".category=" + CATEGORY_TABLE_NAME + ".category_name and " +
									  TODO_TABLE_NAME + ".user='" + username + "' and " + CATEGORY_TABLE_NAME + ".user='" + username + "' and " +
									  LOCATION_TABLE_NAME + ".user='" + username + "' and " + DATE_TABLE_NAME + ".user='" + username + "' and (" + 
									  TODO_TABLE_NAME + ".title LIKE '%" + searchTerm + "%' or " + TODO_TABLE_NAME + ".description LIKE '%" + searchTerm + "%')";

			//String[] query_values = {username, username, username, username, searchTerm, searchTerm};
			Cursor c = this.getReadableDatabase().rawQuery(getToDoListQuery, null);

			c.moveToNext();
			Date currentDate = null;

			while(!c.isAfterLast()) {
				currentDate = new Date(Integer.parseInt(c.getString(c.getColumnIndexOrThrow("year"))),
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("month"))) - 1,
									   Integer.parseInt(c.getString(c.getColumnIndexOrThrow("day"))));



				toDoItems.add(new ToDoItem(c.getString(c.getColumnIndexOrThrow("title")),
										   c.getString(c.getColumnIndexOrThrow("description")),
										   currentDate, new Category(c.getString(c.getColumnIndexOrThrow("category"))),
										   new GeoPoint(new Double(c.getDouble(c.getColumnIndexOrThrow("latitude"))*1000000).intValue(), 
											   		    new Double(c.getDouble(c.getColumnIndexOrThrow("longitude"))*1000000).intValue()), 
										   false, c.getInt(c.getColumnIndexOrThrow("todoID"))));
				c.moveToNext();
			}
		}

		return toDoItems;
	}
}
